package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.OrgDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by xu-jiaHui on 2017/2/25.
 */

public class OrgRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<OrgDto> getOrg4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "select * from tb_org o where ";
        if (actorId != 1)
            sql += " exists (select * from tb_org_father_and_child oa where o.orgId=oa.orgId and oa.MainOrgId=" + orgId + ") and";
        sql += " concat(OrgName,OrgId) like '%" + dtRequestDto.getSearch() + "%'  order by OrgId desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(OrgDto.class));
        List<OrgDto> orgDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return orgDtos;
    }

    public Integer getCount4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId) throws Exception {
        String sql = "select count(*) from tb_org o where ";
        if (actorId != 1)
            sql += "exists (select * from tb_org_father_and_child oa where o.orgId=oa.orgId and oa.MainOrgId=" + orgId + ") and";
        sql += " concat(OrgName,OrgId) like '%" + dtRequestDto.getSearch() + "%'  order by OrgId desc";
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }


    public List<OrgDto> getCandidateMainOrgIdAndNameByOrgId(DTRequestDto dtRequestDto, Integer orgId) throws Exception {
        String sql = "SELECT  o.orgId,o.orgName FROM tb_org o WHERE NOT EXISTS(SELECT * FROM tb_org_father_and_child WHERE " +
                "MainOrgId=o.OrgId and OrgId=" + orgId + ") and o.orgId<>" + orgId + " and o.orgType=1 and o.OrgName like '%"
                + dtRequestDto.getSearch() + "%' order by o.orgId desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(OrgDto.class));
        List<OrgDto> orgDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return orgDtos;
    }

    public Integer getCandidateMainOrgCountByOrgId(DTRequestDto dtRequestDto, Integer orgId) throws Exception {
        String sql = "SELECT  count(*) FROM tb_org o WHERE NOT EXISTS(SELECT * FROM tb_org_father_and_child WHERE " +
                "MainOrgId=o.OrgId and OrgId=" + orgId + ") and o.orgId<>" + orgId + " and o.orgType=1 and o.OrgName " +
                "like '%" + dtRequestDto.getSearch() + "%' order by o.orgId desc";
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<OrgDto> getOrg4Select(Integer actorId, Integer orgId) throws Exception {
        String sql = "select orgId,orgName,orgType from tb_org where orgType=0 ";
        if (actorId != 1)
            sql += " and orgId in(select OrgId from tb_org_father_and_child where mainOrgId=" + orgId + ") ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(OrgDto.class));
        return query.getResultList();
    }

}
